package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.RecordCourse;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 * 记上课 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2023-08-17
 */
public interface RecordCourseMapper extends BaseMapper<RecordCourse> {

    @Select({"<script>",
            "SELECT",
            "GROUP_CONCAT( lesson_num ORDER BY start_date_time) AS lesson_num ",
            "FROM",
            "record_course ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND ((`status` = 1 ) OR ( `status` = 3 ) OR ( `status` = 2 AND is_deduct_house = 1 AND is_deduct_money = 1 )) ",
            "AND subjects_id = ${subjectsId} ",
            "AND end_date_time &lt;= #{dateTimeNow} ",
            "AND deleted = 0 ",
            "AND student_id = ${studentId} ",
            "AND lesson_num IS NOT NULL ",
            "</script>"})
    public String getYetLessonNumStr(@Param("yearPart") Integer yearPart,
                                     @Param("quarterNum") Integer quarterNum,
                                     @Param("subjectsId") Long subjectsId,
                                     @Param("lessonType") Integer lessonType,
                                     @Param("studentId") Long studentId,
                                     @Param("dateTimeNow") String dateTimeNow
    );

    @Select({"<script>",
            "select count(id) AS count  from (SELECT",
            "t1.*",
            "FROM",
            "record_course AS t1",
            "LEFT JOIN cha_study_plan AS t2 ON t1.student_id = t2.student_id ",
            "AND t1.year_part = t2.year_part ",
            "AND t1.`quarter` = t2.quarter_num ",
            "AND t1.lesson_num = t2.lesson_num ",
            "AND t2.lesson_type = 2 ",
            "AND t2.subjects_id = 1 ",
            "AND t2.status = 1 ",
            "and t2.category_id = 1",
            "AND date( t2.created_at ) &lt; #{nowLocalDate}",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.`quarter` = ${quarterNum} ",
            "AND t1.lesson_type = 1 ",
            "AND t1.`status` = 1 ",
            "AND t1.`end_date_time` &lt;= #{startDateTime} ",
            "AND t1.subjects_id = 1 ",
            "AND t1.deleted = 0 ",
            "AND t2.id IS NULL ",
            "and t1.student_id = ${studentId}",
            "GROUP BY",
            "t1.student_id) as t",
            "</script>"})
    public Integer getNotPassCount(@Param("yearPart") Integer yearPart,
                                   @Param("quarterNum") Integer quarterNum,
                                   @Param("studentId") Long studentId,
                                   @Param("nowLocalDate") String nowLocalDate,
                                   @Param("startDateTime") String startDateTime
    );

}
